drop table if exists dws.dim_parent_child_org_rel_info;
create table if not exists dws.dim_parent_child_org_rel_info
(
    org_id         varchar(100),
    org_name       varchar(1000),
    child_org_id   varchar(100),
    child_org_name varchar(1000)
)
;

drop table if exists dws.org_enterprise_temp;
create table if not exists dws.org_enterprise_temp
(
    partition_key varchar(32),
    org_id        varchar(32),
    parent_org_id varchar(32),
    org_name      varchar(100),
    enterprise_id varchar(32),
    org_is_valid  int2
);

drop table if exists dws.org_enterprise_temp_02;
create table if not exists dws.org_enterprise_temp_02
(
    emp_org_id        varchar(32),
    emp_org_name      varchar(100),
    emp_parent_org_id varchar(32)
);

drop table if exists dws.org_enterprise_temp_03;
create table if not exists dws.org_enterprise_temp_03
(
    org_id        varchar(32),
    org_name      varchar(100),
    parent_org_id varchar(32)
);

drop table if exists dws.org_enterprise_temp_04;
create table if not exists dws.org_enterprise_temp_04
(
    org_id        varchar(32),
    org_name      varchar(100),
    parent_org_id varchar(32)
);

create or replace function dws.get_all_child_org(out out_org_id varchar, out out_org_name varchar) returns setof record as
$$
declare
    cur_org_id   varchar ;
    cur_org_name varchar ;
    v_rec        record;
begin
    for cur_org_id,cur_org_name in (select distinct org_id, org_name
                                    from dws.org_enterprise_temp
                                    where org_is_valid = 1
                                    order by org_id)
        loop
            for v_rec in (
                with recursive org_results as (
                    select org_id, org_name, parent_org_id
                    from dws.org_enterprise_temp
                    where org_id = cur_org_id
                    union all
                    select org_id, org_name, parent_org_id
                    from dws.org_enterprise_temp o2
                             inner join org_results res on o2.parent_org_id = res.org_id
                )
                select org_id, org_name
                from org_results
                order by org_id
            )
                loop
                    insert into dws.dim_parent_child_org_rel_info
                    values (cur_org_id, cur_org_name, v_rec.org_id, v_rec.org_name);
                    out_org_id := v_rec.org_id;
                    out_org_name := v_rec.org_name;
                    return next;
                end loop;
        end loop;
    return;
end;
$$ language plpgsql;

select dws.get_all_child_org();

insert into dws.org_enterprise_temp_03
select org_id, org_name, parent_org_id
from dws.org_enterprise_temp
where org_id = '001'
union all
select tmp.org_id, tmp.org_name, tmp.parent_org_id
from (
         select org_id, org_name, parent_org_id
         from dws.org_enterprise_temp_04
         union all
         select emp_org_id, emp_org_name, emp_parent_org_id
         from dws.org_enterprise_temp_02 o2
     ) tmp